/**
 * Copyright (c) 2011-2013, kidzhou 周磊 (zhouleib1412@gmail.com)
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.jfinal.ext.kit;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;


import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

public class PoiKit {

    private SheetData[] sheetDatas;

    public PoiKit(SheetData... sheetDatas) {
        this.sheetDatas = sheetDatas;
    }

    public static PoiKit sheetDatas(SheetData... sheetDatas) {
        return new PoiKit(sheetDatas);
    }

    public Workbook export() {

        HSSFWorkbook wb = new HSSFWorkbook();

        //标题行的style
        CellStyle titleCellStyle = wb.createCellStyle();
        titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER);                //居中
        titleCellStyle.setWrapText(true);                                    //自动换行
        Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);                        //加粗
        font.setFontName("微软雅黑");
        titleCellStyle.setFont(font);

        //内容行的style
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);    //垂直居中
        cellStyle.setWrapText(true);
        Font font2 = wb.createFont();
        font2.setFontName("微软雅黑");
        cellStyle.setFont(font2);

        //多张sheet
        for (SheetData sheetData : sheetDatas) {

            List<Pair> titles = sheetData.titles;
            List<Record> records = sheetData.records;
            String sheetName = sheetData.sheetName;
            HSSFSheet sheet = wb.createSheet(sheetName);

            int rowIndex = 0, cellIndex = 0;

            HSSFRow row = null;
            HSSFCell cell = null;

            //创建标题行
            row = sheet.createRow(rowIndex);
            row.setHeight((short) 450);
            rowIndex++;

            for (Pair pair : titles) {

                cell = row.createCell(cellIndex);
                cell.setCellStyle(titleCellStyle);                //设置样式
                cellIndex++;

                cell.setCellValue(pair.title);
            }

            //处理每一行
            for (Record record : records) {

                row = sheet.createRow(rowIndex);
                row.setHeight((short) 450);
                rowIndex++;
                cellIndex = 0;

                for (Pair pair : titles) {

                    cell = row.createCell(cellIndex);
                    cell.setCellStyle(cellStyle);                //设置样式
                    cellIndex++;

                    Object value = record.get(pair.column);

                    if (value != null) {

                        cell.setCellValue(value.toString());
                    }
                }
            }
        }
        return wb;
    }

    public static  Object[][]  ReadExcel(String filename,String sheetName) {
        Object[][] paras = null;
        try {
            @SuppressWarnings("resource")
            HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(
                    filename));
            HSSFSheet sheet = wookbook.getSheet(sheetName);
            HSSFRow row0=sheet.getRow(0);
            int rows = sheet.getPhysicalNumberOfRows();
            int cols =row0.getPhysicalNumberOfCells();
            paras = new Object[rows][cols];
            for (int i = 0 ; i < rows; i++) {
                HSSFRow row = sheet.getRow(i);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    for (int j = 0; j < cells; j++) {
                        HSSFCell cell = row.getCell(j);
                        if (cell != null) {
                            switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_FORMULA:
                                    break;
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    paras[i][j] = cell.getNumericCellValue();
                                    break;
                                case HSSFCell.CELL_TYPE_STRING:
                                    paras[i][j] = cell.getStringCellValue();
                                    break;
                                default:
                                    paras[i][j] = null;
                                    break;
                            }
                        }
                    }
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
        return paras;
    }


    /**
     * 标题列和列名的对应
     */
    public static class Pair {
        public String column;
        public String title;
        public Pair(String column, String title) {
            this.column = column;
            this.title = title;
        }
    }

    /**
     * 创建一个sheet需要的数据
     */
    public static class SheetData {
        public List<Pair> titles;
        public List<Record> records;
        public String sheetName;

        public SheetData(List<Pair> titles, List<Record> records, String sheetName) {
            this.titles = titles;
            this.records = records;
            this.sheetName = sheetName;
        }
    }



}
